﻿create PROCEDURE Sync.SP_CustomerPhones_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CustomerPhones_Temp TABLE 
		(
			PhoneID UniqueIdentifier,
			[CustomerID] uniqueidentifier,
				[PhoneType] tinyint,
				[PhoneNumber] nchar(12),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CustomerPhones_Temp 
	   SELECT	Tbl.Col.value('PhoneID[1]','uniqueidentifier') PhoneID,
                Tbl.Col.value('CustomerID[1]', 'uniqueidentifier') [CustomerID],
Tbl.Col.value('PhoneType[1]', 'tinyint') [PhoneType],
Tbl.Col.value('PhoneNumber[1]', 'nchar(12)') [PhoneNumber],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CustomerPhones') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CustomerPhones] ([PhoneID],[CustomerID],[PhoneType],[PhoneNumber])
	SELECT t.[PhoneID],t.[CustomerID],t.[PhoneType],t.[PhoneNumber]
	FROM  @CustomerPhones_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CustomerPhones_Tracking s
        WHERE t.PhoneID = s.PhoneID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CustomerPhones]
	SET [CustomerPhones].[CustomerID] = t.[CustomerID],[CustomerPhones].[PhoneType] = t.[PhoneType],[CustomerPhones].[PhoneNumber] = t.[PhoneNumber] 
	FROM [dbo].[CustomerPhones] s JOIN @CustomerPhones_Temp t 
    ON t.PhoneID = s.PhoneID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CustomerPhones_Tracking r
				  WHERE t.PhoneID = r.PhoneID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CustomerPhones]
    FROM @CustomerPhones_Temp t JOIN [dbo].[CustomerPhones] s
    ON t.PhoneID = s.PhoneID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














